﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Collections;
using System.Windows.Forms;
using WChat;

namespace WChat {

    public class SqlHandle {

        #region 构造函数
 
        private const int MaxPool = 300;//最大连接数
        private const int MinPool = 0;//最小连接数
        private const bool Asyn_Process = true; //设置异步访问数据库
        private const bool Mars = true ;//在单个连接上得到和管理多个、仅向前引用和只读的结果集(ADO.NET2.0)
        private const int Conn_Timeout = 15;//设置连接等待时间
        private const int Conn_Lifetime = 5;//设置连接的生命周期
        private string ConnString = "";//连接字符串
        private SqlConnection SqlDrConn = null;//连接对象

        public SqlHandle() {
            ConnString = GetConnString();
            SqlDrConn = new SqlConnection(ConnString);
        }

        private string GetConnString() {
            return "server=bds249611391.my3w.com;"
                + " integrated security=false;"//判断windows域名 和 uid账号 登陆的方式 false为uid登陆
                + " database=bds249611391_db;"
                + " Max Pool Size=" + MaxPool + ";"
                + " Min Pool Size=" + MinPool + ";"
                + " Connect Timeout=" + Conn_Timeout + ";"
                + " Connection Lifetime=" + Conn_Lifetime + ";"
                + " Asynchronous Processing=" + Asyn_Process + ";"//表示使用异步处理操作 true为异步处理
                + " uid=bds249611391;"
                + " MultipleActiveResultSets=" + Mars + ";"
                + " pwd=hq312453";
        }

        public void Open() {
            try {
                if (SqlDrConn.State == System.Data.ConnectionState.Closed) {
                    SqlDrConn.Open();
                }
            } catch (Exception e) {
                WriteLog.Writelog("与数据库建立连接", e.ToString());
            }
        }

        #endregion

        #region 群控设备操作
        public List<string> SelecUserdevices(string devicename) {
            List<string> list = new List<string>();
            // string tabNam = devicename + "_设备表";
            Open();
            try {
                string sql = "select * from " + devicename;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
            }
            return list;
        }
        /// <summary>
        /// 创建设备表
        /// </summary>
        /// <param name="tabName"></param>
        public void CreaeNewDeviceTab(string tabName) {
            Open();
            try {

                string sql = "create table " + tabName + "(shebeiName char(12) not null);";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {

            }
        }

        public void AddDevice( string tabName ,string device) {
            Open();
            try {
                string sql = "insert into " + tabName + "(shebeiName) VALUES ('"+ device + "');";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {

            }
        }

        //插入一条数据
        public void insertOnedata(string Cname, string Cnum) {
            Open();
            try {
                string sql = "insert into [bds249611391_db].[dbo].[hquser] (account,password) VALUES ('" + Cname + "','" + Cnum + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
            }
        }

        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <param name="acc"></param>
        /// <returns></returns>
        public List<string> selectData(string acc) {
            List<string> str = new List<string>();
            Open();
            try {

                string sql = "select UsePas from Admin_daliang where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
            }
            return str;
        }
        #endregion

        #region 聊天记录表操作相关
        /// <summary>
        ///创建与微信号对应的表
        /// </summary>
        /// <param name="tabName"></param>
        public void CreateNewChatTab(string tabName) {
            Open();
            try {
                string tbna = "ChatLog" + tabName;
                string sql = "create table " + tbna + "(Time datetime not null ,FromUser char(100) ,ToUser char(100) ,CONT char(600) ,Received int,Readed int,Type int,FileName char(400),FileSize char(20),V_Content char(200),V_NickName char(60) ,V_Value char(600),V_UserTicket char(200),_msgid char(1000),MediaId char(1000),TxtUrl char(400));";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("CreateNewChatTab", e.ToString());
            }
        }

        /// <summary>
        /// 存储聊天记录数据
        /// </summary>
        /// <param name="msg"></param>
        public void insertChatLog(string tabName, WXMsg msg) {
            string tbna = "ChatLog" + tabName;
            int read;
            if (msg.Readed) {
                read = 1;
            }
            else {
                read = 0;
            }
            Open();
            try {
                string sql = "insert into " + tbna + "(Time,FromUser ,ToUser,CONT ,Received,Readed,Type,FileName,FileSize,V_Content ,V_NickName,V_Value,V_UserTicket,_msgid ,MediaId,TxtUrl)VALUES ('" + msg.Time + "','" + msg.Fr_NickName + "','" + msg.To_NickName + "','" + msg.Msg + "','" + msg.IsReceive + "','" + read + "','" + msg.Type + "','" + msg.FileName + "','" + msg.FileSize + "','" + msg.V_Content + "','" + msg.V_NickName + "','" + msg.V_Value + "','" + msg.V_UserTicket + "','" + msg.MsgId + "','" + msg.MediaId + "','" + msg.TxtUrl + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();  //连接关闭必须在这个的后面，负责会出现sql语句无法执行的问题
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("存储聊天记录数据", e.ToString());
            }
        }
        /// <summary>
        /// 从特定表获取聊天记录，按时间排序TOP 10
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="to_user"></param>
        /// <returns></returns>
        public List<WXMsg> GetChatLog(string tabName, string to_user) {
            List<WXMsg> list = new List<WXMsg>();
            string tbna = "ChatLog" + tabName;
            try {
                Open();
                string sql = "select Top 10 * from " + tbna + " where ToUser ='" + to_user + "'  order by Time DESC";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                //将结果集信息添加到返回向量中
                while (reader.Read()) {
                    WXMsg msg = new WXMsg();  //就是这个啊，气死我了，因为放到外面，每次添加的数据都一样，

                    msg.Time = Convert.ToDateTime(reader[0]);
                    msg.Fr_NickName = reader[1].ToString().Trim();
                    msg.To_NickName = reader[2].ToString().Trim();
                    msg.Msg = reader[3].ToString().Trim();
                    msg.IsReceive = int.Parse(reader[4].ToString().Trim());
                    msg.Readed = reader[5].ToString().Trim() == "1" ? true : false;
                    msg.Type = reader[6].ToString().Trim();
                    msg.FileName = reader[7].ToString().Trim();
                    msg.FileSize = reader[8].ToString().Trim();
                    msg.V_Content = reader[9].ToString().Trim();
                    msg.V_NickName = reader[10].ToString().Trim();
                    msg.V_Value = reader[11].ToString().Trim();
                    msg.V_UserTicket = reader[12].ToString().Trim();
                    msg.MsgId = reader[13].ToString().Trim();
                    msg.MediaId = reader[14].ToString().Trim();
                    msg.TxtUrl = reader[15].ToString().Trim();
                    list.Add(msg);
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("GetChatLog:获取记录", e.ToString());
            }
            return list;
        }

        /// <summary>
        /// 从特定表获取聊天记录，按时间排序
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="to_user"></param>
        /// <returns></returns>
        public List<WXMsg> GetAllChatLog(string tabName, string to_user) {
            List<WXMsg> list = new List<WXMsg>();
            string tbna = "ChatLog" + tabName;
            try {
                Open();
                string sql = "select TOP 20 * from " + tbna + " where ToUser ='" + to_user + "'  order by Time DESC";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                //将结果集信息添加到返回向量中
                while (reader.Read()) {
                    WXMsg msg = new WXMsg();  //就是这个啊，气死我了，因为放到外面，每次添加的数据都一样，

                    msg.Time = Convert.ToDateTime(reader[0]);
                    msg.Fr_NickName = reader[1].ToString().Trim();
                    msg.To_NickName = reader[2].ToString().Trim();
                    msg.Msg = reader[3].ToString().Trim();
                    msg.IsReceive = int.Parse(reader[4].ToString().Trim());
                    msg.Readed = reader[5].ToString().Trim() == "1" ? true : false;
                    msg.Type = reader[6].ToString().Trim();
                    msg.FileName = reader[7].ToString().Trim();
                    msg.FileSize = reader[8].ToString().Trim();
                    msg.V_Content = reader[9].ToString().Trim();
                    msg.V_NickName = reader[10].ToString().Trim();
                    msg.V_Value = reader[11].ToString().Trim();
                    msg.V_UserTicket = reader[12].ToString().Trim();
                    msg.MsgId = reader[13].ToString().Trim();
                    msg.MediaId = reader[14].ToString().Trim();
                    msg.TxtUrl = reader[15].ToString().Trim();
                    list.Add(msg);
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("GetAllChatLog:从特定表获取聊天记录，按时间排序", e.ToString());
            }
            return list;
        }
        /// <summary>
        /// 从时间删除记录
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="time"></param>
        public void DeleteChatLog(string tabName, string time) {
            Open();
            try {

                string tbna = "ChatLog" + tabName;
                string sql = "delete  from " + tbna + " where Time='" + time + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("DeleteChatLog:删除聊天记录表", e.ToString());
            }
        }

        /// <summary>
        /// 判断表的存在
        /// </summary>
        /// <param name="tabna"></param>
        /// <returns></returns>
        public bool HaveTab(string tabna) {
            bool havtab = false;
            Open();
            try {
                string tbna = "ChatLog" + tabna;
                string sql = "select * from " + tbna;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    havtab = true;
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("HaveTab", e.ToString());
            }
            return havtab;
        }

        //按照微信，好友，时间查找聊天记录
        public List<WXMsg> GetChatLogFriendWitchMe( string ChatName, string FriendName, DateTime dt ) {
            List<WXMsg> L = new List<WXMsg>();
            StringBuilder sb = new StringBuilder(string.Format("select * from ChatLog{0} where ToUser='{1}'", ChatName, FriendName));
            if (dt.ToString() != "0001/1/1 0:00:00") sb.Append(string.Format(" and Time >= '{0}'", dt));
            SqlCommand com = new SqlCommand(sb.ToString(), SqlDrConn);
            SqlDataReader dr;
            try {
                Open();
                dr = com.ExecuteReader();
                while (dr.Read()) {
                    WXMsg msg = new WXMsg();
                    msg.Time = Convert.ToDateTime(dr[0]);
                    msg.Fr_NickName = dr[1].ToString().Trim();
                    msg.To_NickName = dr[2].ToString().Trim();
                    msg.Msg = dr[3].ToString().Trim();
                    msg.IsReceive = int.Parse(dr[4].ToString().Trim());
                    msg.Readed = dr[5].ToString().Trim() == "1" ? true : false;
                    msg.Type = dr[6].ToString().Trim();
                    msg.FileName = dr[7].ToString().Trim();
                    msg.FileSize = dr[8].ToString().Trim();
                    msg.V_Content = dr[9].ToString().Trim();
                    msg.V_NickName = dr[10].ToString().Trim();
                    msg.V_Value = dr[11].ToString().Trim();
                    msg.V_UserTicket = dr[12].ToString().Trim();
                    msg.MsgId = dr[13].ToString().Trim();
                    msg.MediaId = dr[14].ToString().Trim();
                    msg.TxtUrl = dr[15].ToString().Trim();
                    L.Add(msg);
                }
                dr.Close();
                com.Dispose();
                SqlDrConn.Close();
                return L;
            }
            catch (Exception) {
                com.Dispose();
                SqlDrConn.Close();
                return L;
            }
        }

        /// <summary>
        /// 判断表的存在
        /// </summary>
        /// <param name="tabna"></param>
        /// <returns></returns>
        public bool HaveTheTab(string prefix, string tabna) {
            bool havtab = false;
            Open();
            try {
                string tbna = prefix + tabna;
                string sql = "select * from " + tbna;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    havtab = true;
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("HaveTheTab", e.ToString());
            }
            return havtab;
        }

        /// <summary>
        /// </summary>
        /// <param name="touser"></param>
        /// <returns></returns>
        public List<string> GetUsrNaFrChlo( string tabna ) {
            List<string> list = new List<string>();
            string tbna = "ChatLog" + tabna;
            Open();
            try {

                string sql = "select ToUser from " + tbna;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    list.Add((reader[0].ToString().Trim()));
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("GetUsrNaFrChlo", e.ToString());
            }
            return list;
        }

        /// <summary>
        /// 获取时间从touser和和tabNam
        /// </summary>
        /// <param name="tabna"></param>
        /// <returns></returns>
        public List<DateTime> GetTimFroToUseAndTabNaInChaLog( string tabna, string Touser ) {
            List<DateTime> list = new List<DateTime>();
            string tbna = "ChatLog" + tabna;
            Open();
            try {
                string sql = "select Time from " + tbna + " where ToUser='" + Touser + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    list.Add(((DateTime)reader[0]));
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("GetTimFroToUseAndTabNaInChaLog", e.ToString());
            }
            return list;
        }

        /// <summary>
        ///  获取聊天记录从touser和time和tabNam
        /// </summary>where ToUser= '" + Touser +" 'and datediff(day,Time,'"+ time +"')=0 "
        /// <param name="tabName"></param>
        /// <param name="Touser"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        public List<WXMsg> GetCLogFroToUseAndTimNaInChaLog( string tabName, string Touser, string time ) {
            List<WXMsg> list = new List<WXMsg>();
            string tbna = "ChatLog" + tabName;
            // MessageBox.Show(tbna + Touser + time);
            try {
                Open();
                string sql = "select * from " + tbna + " where ToUser = '" + Touser + " ' and datediff( day, Time,'" + time + "')= 0";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                //将结果集信息添加到返回向量中
                while (reader.Read()) {
                    WXMsg msg = new WXMsg();  //就是这个啊，气死我了，因为放到外面，每次添加的数据都一样，

                    msg.Time = Convert.ToDateTime(reader[0]);
                    msg.Fr_NickName = reader[1].ToString().Trim();
                    msg.To_NickName = reader[2].ToString().Trim();
                    msg.Msg = reader[3].ToString().Trim();
                    msg.IsReceive = int.Parse(reader[4].ToString().Trim());
                    msg.Readed = reader[5].ToString().Trim() == "1" ? true : false;
                    msg.Type = reader[6].ToString().Trim();
                    msg.FileName = reader[7].ToString().Trim();
                    msg.FileSize = reader[8].ToString().Trim();
                    msg.V_Content = reader[9].ToString().Trim();
                    msg.V_NickName = reader[10].ToString().Trim();
                    msg.V_Value = reader[11].ToString().Trim();
                    msg.V_UserTicket = reader[12].ToString().Trim();
                    msg.MsgId = reader[13].ToString().Trim();
                    msg.MediaId = reader[14].ToString().Trim();
                    msg.TxtUrl = reader[15].ToString().Trim();
                    list.Add(msg);
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("GetCLogFroToUseAndTimNaInChaLog", e.ToString());
            }
            return list;
        }
        /// <summary>
        /// 从特定表搜索聊天记录，按时间排序
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="to_user"></param>
        /// <returns></returns>
        public List<WXMsg> SearchChatlog( string tabName, string Cont ,DateTime dt) {
            List<WXMsg> list = new List<WXMsg>();
            string tbna = "ChatLog" + tabName;
            try {
                Open();
                StringBuilder sb = new StringBuilder(string.Format("select * from {0} where CONT like '%{1}%'", tbna, Cont));
                if (dt.ToString() != "0001/1/1 0:00:00") sb.Append(string.Format(" and Time >= '{0}'", dt));
                SqlCommand cmd = new SqlCommand(sb.ToString(), SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                //将结果集信息添加到返回向量中
                while (reader.Read()) {
                    WXMsg msg = new WXMsg();  //就是这个啊，气死我了，因为放到外面，每次添加的数据都一样，

                    msg.Time = Convert.ToDateTime(reader[0]);
                    msg.Fr_NickName = reader[1].ToString().Trim();
                    msg.To_NickName = reader[2].ToString().Trim();
                    msg.Msg = reader[3].ToString().Trim();
                    msg.IsReceive = int.Parse(reader[4].ToString().Trim());
                    msg.Readed = reader[5].ToString().Trim() == "1" ? true : false;
                    msg.Type = reader[6].ToString().Trim();
                    msg.FileName = reader[7].ToString().Trim();
                    msg.FileSize = reader[8].ToString().Trim();
                    msg.V_Content = reader[9].ToString().Trim();
                    msg.V_NickName = reader[10].ToString().Trim();
                    msg.V_Value = reader[11].ToString().Trim();
                    msg.V_UserTicket = reader[12].ToString().Trim();
                    msg.MsgId = reader[13].ToString().Trim();
                    msg.MediaId = reader[14].ToString().Trim();
                    msg.TxtUrl = reader[15].ToString().Trim();
                    list.Add(msg);
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("SearchChatlog", e.ToString());
            }
            return list;
        }
        #endregion

        //聊天记总页数
        public int GetChatLogPageCount( string ChatName, string Contact, DateTime dt ) {
            StringBuilder sb = new StringBuilder(string.Format("select count(1) from ChatLog{0} where CONT like '%{1}%'", ChatName, Contact));
            if (dt.ToString() != "0001/1/1 0:00:00") sb.Append(string.Format(" and Time >='{0}'", dt));
            SqlCommand com = new SqlCommand(sb.ToString(), SqlDrConn);
            int i = 0;
            try {
                Open();
                i = Convert.ToInt32(com.ExecuteScalar());
                com.Dispose();
                SqlDrConn.Close();
                return i;
            }
            catch (Exception) {
                com.Dispose();
                SqlDrConn.Close();
                return -1;
            }
        }

        //单页查找聊天记录
        public List<WXMsg> GetChatLogNumPage( string ChatName, string Contact, int pageChatLogNum, int page, DateTime dt ) {
            List<WXMsg> L = new List<WXMsg>();
            #region 判断存储过程是否存在
            string sql3 = string.Format("if OBJECT_ID('ChatLog_Search', 'p') is not null"
                    + " drop procedure ChatLog_Search");
            SqlCommand com = new SqlCommand(sql3, SqlDrConn);
            Open();
            com.ExecuteNonQuery();
            com.Dispose();
            #endregion
            #region 创建存储过程
            StringBuilder sb = new StringBuilder(string.Format(
                "CREATE PROCEDURE ChatLog_Search " + "\n"
                + " as" + "\n"
                + " declare @table Table" + "\n"
                + " ( " + "\n"
                    + " Time datetime," + "\n"
                    + " FromUser char(100)," + "\n"
                    + " ToUser char(100)," + "\n"
                    + " CONT char(600)," + "\n"
                    + " Received int," + "\n"
                    + " Readed int," + "\n"
                    + " Type int," + "\n"
                    + " FileName char(400)," + "\n"
                    + " FileSize char(100)," + "\n"
                    + " V_Content char(200)," + "\n"
                    + " V_NickName char(100)," + "\n"
                    + " V_Value char(600)," + "\n"
                    + " V_UserTicket char(200)," + "\n"
                    + " _msgid char(1000)," + "\n"
                    + " MediaId char(1000)," + "\n"
                    + " TxtUrl char(400)" + "\n"
                    + " ) " + "\n"
                + " insert into @table select * from ChatLog{0} where CONT like '%{1}%' and Time >= '{2}'" + "\n"
                + " select top {3} * from @table where Time not in (select top {4} Time from @table)", ChatName, Contact, dt, pageChatLogNum, pageChatLogNum * (page - 1)));
            SqlCommand com2 = new SqlCommand(sb.ToString(), SqlDrConn);
            try {
                com2.ExecuteNonQuery();
                com2.Dispose();
            }
            catch (Exception) {
                com2.Dispose();
                SqlDrConn.Close();
                throw;
            }
            #endregion
            #region 执行存储过程
            string sql = "execute ChatLog_Search";
            SqlCommand com3 = new SqlCommand(sql, SqlDrConn);
            SqlDataReader dr;
            string a = "";
            try {
                Open();
                dr = com3.ExecuteReader();
                while (dr.Read()) {
                    WXMsg msg = new WXMsg();
                    msg.Time = Convert.ToDateTime(dr["Time"]);
                    msg.Fr_NickName = dr["FromUser"].ToString().Trim();
                    msg.To_NickName = dr["ToUser"].ToString().Trim();
                    msg.Msg = dr["CONT"].ToString().Trim();
                    msg.IsReceive = int.Parse(dr["Received"].ToString().Trim());
                    msg.Readed = dr["Readed"].ToString().Trim() == "1" ? true : false;
                    msg.Type = dr["Type"].ToString().Trim();
                    msg.FileName = dr["FileName"].ToString().Trim();
                    msg.FileSize = dr["FileSize"].ToString().Trim();
                    msg.V_Content = dr["V_Content"].ToString().Trim();
                    msg.V_NickName = dr["V_NickName"].ToString().Trim();
                    msg.V_Value = dr["V_Value"].ToString().Trim();
                    msg.V_UserTicket = dr["V_UserTicket"].ToString().Trim();
                    msg.MsgId = dr["_msgid"].ToString().Trim();
                    msg.MediaId = dr["MediaId"].ToString().Trim();
                    msg.TxtUrl = dr["TxtUrl"].ToString().Trim();
                    L.Add(msg);
                }
                dr.Close();
                com3.Dispose();
                SqlDrConn.Close();
                return L;
            }
            catch (Exception) {
                com3.Dispose();
                SqlDrConn.Close();
                //MessageBox.Show(a);
                throw;
            }
            #endregion
        }
        //分页查找
        //数据总数
        public int SearchChatLogCount( string ChatName, string FriendName, DateTime dt ) {
            StringBuilder sb = new StringBuilder(string.Format("select count(1) from ChatLog{0} where ToUser='{1}'", ChatName, FriendName));
            if (dt.ToString() != "0001/1/1 0:00:00") sb.Append(string.Format(" and Time >='{0}'", dt));
            SqlCommand com = new SqlCommand(sb.ToString(), SqlDrConn);
            Open();
            int i = 0;
            try {
                i = Convert.ToInt32(com.ExecuteScalar());
                com.Dispose();
                SqlDrConn.Close();
                return i;
            }
            catch (Exception) {
                com.Dispose();
                SqlDrConn.Close();
                return i;
            }

        }
        //单页20条数据
        public List<WXMsg> SearchNumChatLog( string ChatName, string FriendName, int pageChatLogNum, int page, DateTime dt ) {
            List<WXMsg> L = new List<WXMsg>();
            StringBuilder sb = new StringBuilder(string.Format("select Top {0} * from ChatLog{1} where Time not in (select Top {2} Time from ChatLog{3} where ToUser='{4}') and ToUser='{5}'", pageChatLogNum, ChatName, pageChatLogNum * (page - 1), ChatName, FriendName, FriendName));
            if (dt.ToString() != "0001/1/1 0:00:00") sb.Append(string.Format(" and Time >='{0}'", dt));
            SqlCommand com = new SqlCommand(sb.ToString(), SqlDrConn);
            SqlDataReader dr;
            try {
                Open();
                dr = com.ExecuteReader();
                while (dr.Read()) {
                    WXMsg w = new WXMsg();
                    w.Time = Convert.ToDateTime(dr["Time"]);
                    w.Fr_NickName = dr["FromUser"].ToString().Trim();
                    w.To_NickName = dr["ToUser"].ToString().Trim();
                    w.Msg = dr["CONT"].ToString().Trim();
                    w.IsReceive = int.Parse(dr["Received"].ToString().Trim());
                    w.Readed = dr["Readed"].ToString().Trim() == "1" ? true : false;
                    w.Type = dr["Type"].ToString().Trim();
                    w.FileName = dr["FileName"].ToString().Trim();
                    w.FileSize = dr["FileSize"].ToString().Trim();
                    w.V_Content = dr["V_Content"].ToString().Trim();
                    w.V_NickName = dr["V_NickName"].ToString().Trim();
                    w.V_Value = dr["V_Value"].ToString().Trim();
                    w.V_UserTicket = dr["V_UserTicket"].ToString().Trim();
                    w.MsgId = dr["_msgid"].ToString().Trim();
                    w.MediaId = dr["MediaId"].ToString().Trim();
                    w.TxtUrl = dr["TxtUrl"].ToString().Trim();
                    L.Add(w);
                }
                dr.Close();
                com.Dispose();
                SqlDrConn.Close();
                return L;
            }
            catch (Exception) {
                com.Dispose();
                SqlDrConn.Close();
                return L;
            }
        }
         

        #region 智能回复
        //创建智能回复数据表
        public void Create_reply_tab(string tabName) {
            try {
                Open();
                string sql = "create table " + tabName + "_智能回复表(Time nvarchar(MAX) not null,Keyword nvarchar(MAX) not null,Contents nvarchar(MAX) not null);";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                //cmd.Connection;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        //添加智能回复的数据
        public void insert_reply_data(string tabName, string time, string key, string text) {
            try {
                Open();
                string sql = "insert into " + tabName + "_智能回复表 (Time,Keyword,Contents) VALUES ('" + time + "','" + key + "','" + text + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        /// <summary>
        /// 查询回复关键词
        /// </summary>
        /// <param name="tabName"></param>
        /// <returns></returns>
        public List<string> select_key(string tabName) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select Keyword from " + tabName + "_智能回复表";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("select_key", e.ToString());
            }
            return str;
        }

        /// <summary>
        /// 查询回复内容
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public List<string> select_content(string tabName, string key) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select Contents from " + tabName + "_智能回复表 where Keyword = '" + key + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("select_content", e.ToString());
            }
            return str;
        }

        //删除记录
        public void Delete_reply_data(string tabName, string time) {
            try {
                Open();
                string sql = "delete  from " + tabName + "_智能回复表 where Time='" + time + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }
        #endregion

        #region 杂项
        /// <summary>
        /// </summary>
        /// <param name="touser"></param>
        /// <returns></returns>
        public List<string> GetUserName() {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select * from [ChatLog].[dbo].[1334463622]";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    list.Add((reader[1].ToString().Trim()));
                }

                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }


        public void selectAllCargoInnnnfor(string tabName) {
            try {
                Open();
                string sql = "create table " + tabName + "(account char(32) not null,password int not null,);";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }
        public bool insertCargoInfo(string Cname, string Cnum) {
            try {
                Open();
                string sql = "insert into hquser (account,password) values ('" + Cname + "','" + Cnum + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
                return true;
            }
            catch (Exception) {
                return false;
            }
        }
        public bool deleteCargoInfo(string Cno) {
            try {
                Open();
                string sql = "delete from C where Cno=" + Cno;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
                return true;
            }
            catch (Exception) {
                return false;
            }
        }
        #endregion

        #region 子账户表操作
        /// <summary>
        /// 创建子账户表
        /// </summary>
        /// <param name="tabName"></param>
        public void CreChiAccTab(string tabName) {
            try {
                Open();
                string tbna = "Admin_" + tabName;
                string sql = "create table " + tbna + "( Iden int identity(1,1), UseAcct char(50) PRIMARY KEY , UsePas char(50), RegiTime char(50) not null,CanChat int ,CanViNots int,CanMass int,CanRob int,CanAddWx int,CanLooChaLog int,CanQuickReply int );";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        /// <summary>
        /// 创建子账户总表，所有子账户全在这里
        /// </summary>
        /// <param name="tabName"></param>
        public void CreAllChiAccTab(string tabName) {
            try {
                Open();
                string tbna = "Child_" + tabName;
                string sql = "create table " + tbna + "( Iden int identity(1,1), UseAcct char(50) PRIMARY KEY , UsePas char(50), RegiTime char(50) not null);";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="msg"></param>
        public void AddChiAcc(string tabName, ChiAcc msg) {
            try {
                Open();
                //string sql = "insert into [bds249611391_db].[dbo].[Admin_" + tabName + "] (UseAcct, UsePas,RegiTime,CanChat,CanViNots,CanMass,CanRob,CanAddWx,CanLooChaLog, CanQuickReply ) VALUES( '" + msg.UseAcct + "','" + msg.UsePass + "','" + msg.RegiTime + "','" + msg.CanChat + "','" + msg.CanViNots + "','" + msg.CanMass + "','" + msg.CanRob + "','" + msg.CanAddWx + "','" + msg.CanLooChaLog + "','" + msg.CanQuickReply + "')";
                string sql = string.Format("insert into Admin_{0} (UseAcct,UsePas,RegTime,CanChat,CanViNots,CanMass,CanRob,CanAddWx,CanLooChaLog,CanQuickReply) values ('{1}','{2}','{3}',{4},{5},{6},{7},{8},{9},{10})", tabName,msg.UseAcct,msg.UsePass,msg.RegiTime,msg.CanChat,msg.CanViNots,msg.CanMass,msg.CanRob,msg.CanAddWx,msg.CanLooChaLog,msg.CanQuickReply);
                // string sql = "insert into" + tbna + "(UseAcct, UsePas,RegiTime,CanChat,CanViNots,CanMass,CanRob,CanAddWx,CanLooChaLog, CanQuickReply ) VALUES ( '" + msg.UseAcct + "','" + msg.UsePass + "','" + msg.RegiTime + "','" + msg.CanChat + "','" + msg.CanViNots + "','" + msg.CanMass + "','" + msg.CanRob + "','" + msg.CanAddWx + "','" + msg.CanLooChaLog + "','" + msg.CanQuickReply + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        /// <summary>
        /// 更新账户
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="msg"></param>
        public void UpdateChiAcc(string tabName, string account, ChiAcc msg) {
            try {
                Open();
                string sql = "update [bds249611391_db].[dbo].[Admin_" + tabName + "] set UsePas='" + msg.UsePass + "',CanChat='" + msg.CanChat + "',CanViNots='" + msg.CanViNots + "',CanMass='" + msg.CanMass + "',CanRob='" + msg.CanRob + "',CanAddWx='" + msg.CanAddWx + "',CanLooChaLog='" + msg.CanLooChaLog + "', CanQuickReply='" + msg.CanQuickReply + "'  where UseAcct='" + account + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        public ChiAcc GetChiInfo(string tabName, string ChiAcc) {
            ChiAcc ca = new ChiAcc();
            try {
                Open();
                string sql = "select * from [bds249611391_db].[dbo].[Admin_" + tabName + "] where UseAcct ='" + ChiAcc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    ca.UseAcct = reader[1].ToString().Trim();
                    ca.UsePass = reader[2].ToString().Trim();
                    ca.RegiTime = Convert.ToDateTime(reader[3]); ;
                    ca.CanChat = int.Parse(reader[4].ToString().Trim());
                    ca.CanAddWx = int.Parse(reader[5].ToString().Trim());
                    ca.CanLooChaLog = int.Parse(reader[6].ToString().Trim());
                    ca.CanQuickReply = int.Parse(reader[7].ToString().Trim());
                    ca.CanRob = int.Parse(reader[8].ToString().Trim());
                    ca.CanViNots = int.Parse(reader[9].ToString().Trim());
                    ca.CanMass = int.Parse(reader[10].ToString().Trim());
                }

                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return ca;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="ChiAcc"></param>
        /// <returns></returns>
        public List<ChiAcc> GetAllChiAcc(string tabName) {
            List<ChiAcc> l = new List<WChat.ChiAcc>();
            try {
                Open();
                string sql = "select * from [bds249611391_db].[dbo].[Admin_" + tabName + "]";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    ChiAcc ca = new ChiAcc();
                    ca.UseAcct = reader[1].ToString().Trim();
                    ca.UsePass = reader[2].ToString().Trim();
                    ca.RegiTime = Convert.ToDateTime(reader[3]); ;
                    ca.CanChat = int.Parse(reader[4].ToString().Trim());
                    ca.CanAddWx = int.Parse(reader[5].ToString().Trim());
                    ca.CanLooChaLog = int.Parse(reader[6].ToString().Trim());
                    ca.CanQuickReply = int.Parse(reader[7].ToString().Trim());
                    ca.CanRob = int.Parse(reader[8].ToString().Trim());
                    ca.CanViNots = int.Parse(reader[9].ToString().Trim());
                    ca.CanMass = int.Parse(reader[10].ToString().Trim());
                    l.Add(ca);
                }

                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return l;
        }
        public int GetAccNum(string tabName) {
            int i = 0;
            try {
                Open();
                string sql = "select * from Admin_" + tabName;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    i++;
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return i;
        }

        public int Delete_ChiAcc(string tabName, string accNa) {
            int i = 0;
            try {
                Open();
                string sql = "delete from Admin_" + tabName + " where UseAcct ='" + accNa + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return i;
        }

        #endregion

        #region  微信个数表

        /// <summary>
        /// 创建微信号个数表
        /// </summary>
        /// <param name="tabName"></param>
        public void CreateNumOfWxTab(string tabName_Acco) {
            string tbna = "NumWx" + tabName_Acco;
            try {
                Open();
                string sql = "create table " + tbna + "(Wx_Name varchar(100) primary key );";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        public void insertWx(string tabName, string MeWxNic) {
            string tbna = "NumWx" + tabName;
            try {
                Open();
                string sql = "insert into " + tbna + "(Wx_Name)VAlUES ('" + MeWxNic + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        public List<string> Select_Num_Wx(string tabName_Acco) {
            List<string> list = new List<string>();
            string tbna = "NumWx" + tabName_Acco;
            try {
                Open();
                string sql = "select * from " + tbna;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }

        #endregion

        #region 总表查询
        /// <summary>
        /// 查询总表账户密码
        /// </summary>
        /// <param name="acc"></param>
        /// <returns></returns>
        public string SelectPassFromHqUser(string acc) {
            string str = "";
            try {
                Open();
                string sql = "select password from hquser where account='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str = reader[0].ToString();
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }

        /// <summary>
        /// 找授予我的子账户
        /// </summary>
        public List<string> Select_account(string acc) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select account from hquser where belong_account='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    //将结果集信息添加到返回向量中"'and account_level='"+ charge_and_Crew + "'",int charge_and_Crew
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }

        //查询一条数据
        public string GetAccAll(string account, int num) {
            string str = null;
            try {
                Open();
                string sql = "select * from hquser where account='" + account + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str = reader[num].ToString();
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }

        //插入一条数据
        public void insertOnedata(string Cname, string Cnum, int level, string belong_account, string depart, int create, int allow_log, int use_message, int view_service, int view_sector, int export_news, int customer_analysis, int use_assistant, int check_all_message, int delete_customer_record, string user_name, string contact, int manager_type) {
            try {
                Open();
                string sql = "insert into hquser (account,password,account_level,belong_account,belong_depart,allow_create,allow_log,use_message,view_service,view_sector,export_news,customer_analysis,use_assistant,check_all_message,delete_customer_record,user_name,contact,manager_type) VALUES ('" + Cname + "','" + Cnum + "','" + level + "','" + belong_account + "','" + depart + "','" + create + "','" + allow_log + "','" + use_message + "','" + view_service + "','" + view_sector + "','" + export_news + "','" + customer_analysis + "','" + use_assistant + "','" + check_all_message + "','" + delete_customer_record + "','" + user_name + "','" + contact + "','" + manager_type + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        #endregion

        #region 群发表操作
        //创建群发数据表
        public void create_AllSend_Tab(string tabName) {
            try {
                Open();
                string sql = "create table " + tabName + "_群发记录表(SendTime datetime not null,name varchar(MAX) not null,wxName varchar(MAX) not null,recipient varchar(MAX) not null,text varchar(MAX) not null,type int not null);";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        /// <summary>
        /// 向群发数据表插入数据
        /// </summary>
        /// <param name="zhanghu">账户</param>
        /// <param name="time">时间</param>
        /// <param name="wxName">微信名</param>
        /// <param name="reci">接受者</param>
        /// <param name="text">内容</param>
        /// <param name="type">类型</param>
        public void insert_AllSend_data(string zhanghu, string time, string wxName, string reci, string text, int type) {
            try {
                Open();
                string sql = "insert into " + zhanghu + "_群发记录表(SendTime,name,wxName,recipient,text,type) values ('" + time + "','" + zhanghu + "','" + wxName + "','" + reci + "','" + text + "','" + type + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }
        //查询群发消息内容
        public List<string> Select_AllSend_Text(string name, string time) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select text from " + name + "_群发记录表 where SendTime='" + time + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }
        //查询群发文本类型
        public List<string> Select_AllSend_Type(string name, string time) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select type from " + name + "_群发记录表 where SendTime='" + time + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }
        #endregion

        #region 微信好友表
      
        //创建微信号 好友表
        public void create_wx_Tab( string tabName ) {
            try {
                Open();
                string sql = "create table " + tabName + "_微信好友表(user_name varchar(MAX) not null,nike_name varchar(MAX) not null,weixin varchar(MAX) not null,wx_name varchar(MAX) not null,Sex char(10) not null，Province varchar(MAX) not null);";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }
        /// <summary>
        /// 清空微信好友表
        /// </summary>
        /// <param name="tabNam"></param>
        /// <returns></returns>
        public bool DeleteMyFriendsTab(string tabNam) {
            try {
                Open();
                string sql = "delete from " + tabNam + "_微信好友表";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
                return true;
            }
            catch (Exception) {
                return false;
            }
        }

        /// <summary>
        /// 微信好友表插入数据
        /// </summary>
        /// <param name="Tabna"></param>
        /// <param name="Fre_userNa"></param>
        /// <param name="Fre_nike"></param>
        /// <param name="My_userNa"></param>
        /// <param name="My_nick"></param>
        /// <param name="sex"></param>
        /// <param name="pro"></param>
        public void InsertMyFriendTab(string Tabna, string My_userNa, string My_nick, string Fre_userNa, string Fre_nick, string Sex,string Pro) {  
            try {
                Open();
            string sql = "insert into " + Tabna + "_微信好友表 ( user_name,nike_name,weixin,wx_name,Sex,Province ) VALUES ('" + My_userNa + "','" + My_nick + "','" + Fre_userNa + "','" + Fre_nick + "','" + Sex + "','"+ Pro +"')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("InsertMyFriendTab", e.ToString());
            }
        }
        #endregion


        #region 统计分析操作
        //设置好友验证状态
        public void update_new_friend(string tabName, string name, string type, int num) {
            // MessageBox.Show("设置:"+tabName);
            string tbna = "ChatLog" + tabName;
            try {
                Open();
                string sql = "update '" + tabName + "' set Readed='" + num + "' where Type='" + type + "' and ToUser = '" + name + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        //查询新朋友
        public bool Select_send_devices(string tabName, string asd) {
            string tbna = "ChatLog" + tabName;
            bool qwe = false;
            try {
                Open();
                string sql = "select * from [bds249611391_db].[dbo].[" + tbna + "] where ToUser='" + asd + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    qwe = true;
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("Select_send_devices", e.ToString());
            }
            return qwe;
        }

        //查找类型为37  状态为1的新朋友
        public List<string> Select_New_Man(string tabName, string type, int asd) {

            string tbna = "ChatLog" + tabName;
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select ToUser from '" + tbna + "' where Readed='" + asd + "' and Type='" + type + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("Select_New_Man", e.ToString());
                //throw;
            }
            return list;
        }

        //查询好友性别
        public List<string> Select_friend_sex(string nike, string tabNam) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select Sex from [bds249611391_db].[dbo].[" + tabNam + "_微信好友表] where Sex='" + nike + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("Select_friend_sex", e.ToString());
            }
            return list;
        }

        #endregion
       
        #region 查询信息
        //查询账号等级
        public List<string> select_account_level(string acc) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select CanChat from [bds249611391_db].[dbo].[hquser] where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("select_account_level", e.ToString());
            }
            return str;
        }
        //查询账户所属
        public List<string> select_belong_account(string acc) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select CanViNots from [bds249611391_db].[dbo].[hquser] where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }
        //是否允许创建子账户
        public List<string> select_allow_create(string acc) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select CanMass from [bds249611391_db].[dbo].[hquser] where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }
        //是否允许登录客户端
        public List<string> select_allow_log(string acc) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select CanRob from [bds249611391_db].[dbo].[hquser] where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }
        //是否允许使用消息管理
        public List<string> select_use_message(string acc) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select CanAddWx from [bds249611391_db].[dbo].[hquser] where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }
        //是否允许查看其他客服
        public List<string> select_view_service(string acc) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select CanLooChaLog from [bds249611391_db].[dbo].[hquser] where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }
        //是否允许查看其他部门
        public List<string> select_view_sector(string acc) {
            List<string> str = new List<string>();
            try {
                Open();
                string sql = "select CanQuickReply from [bds249611391_db].[dbo].[hquser] where UseAcct='" + acc + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    str.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return str;
        }

        #endregion

        #region 其他杂项

        //查询微信号
        public List<string> Select_send_devices(string asd) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select nike_name from [bds249611391_db].[dbo].[" + asd + "_微信好友表]";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }

        //查询好友地域
        public List<string> Select_friend_pro(string asd) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select Province from " + asd + "_微信好友表";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();

            }
            catch (Exception) {
                throw;
            }
            return list;
        }
        //每个地区多少人
        public int SearchProNum(string pro, string asd) {
            int temp = 0;
            Open();
            string sql = "select count(1) from " + asd + "_微信好友表 where Province = '" + pro + "'";
            SqlCommand com = new SqlCommand(sql, SqlDrConn);
            temp = Convert.ToInt32(com.ExecuteScalar());
            SqlDrConn.Close();
            return temp;
        }

        //查询发送人代码
        public string searchMyUserName(string nike, string tabNam) {
            string s = string.Empty;
            try {
                Open();
                string sql = "select user_name from [bds249611391_db].[dbo].[" + tabNam + "_微信好友表] where nike_name='" + nike + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    s = reader["user_name"].ToString();
                    break;
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return s;
        }

        /// <summary>
        /// 查询接收人代码
        /// </summary>
        /// <param name="mynike">微信号昵称</param>
        /// <param name="frinike">好友昵称</param>
        /// <param name="tabNam">表名</param>
        /// <returns></returns>
        public string Select_Send_User(string mynike, string frinike, string tabNam) {
            string userName = string.Empty;
            try {
                Open();
                string sql = "select weixin from [bds249611391_db].[dbo].[" + tabNam + "_微信好友表] where nike_name ='" + mynike + "'and wx_name='" + frinike + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    userName = reader["weixin"].ToString().Trim();
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("Select_Send_User", e.ToString());
            }
            return userName;
        }
        //查询微信号中的联系人
        public List<string> Select_send_man(string name, string asd) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select wx_name from [bds249611391_db].[dbo].[" + asd + "_微信好友表] where nike_name='" + name + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }

                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("Select_send_man", e.ToString());
            }
            return list;
        }
        //账户下有多少个联系人
        public int SearchFriendNum(string asd) {
            int temp = 0;
            Open();
            string sql = "select count(1) from " + asd + "_微信好友表";
            SqlCommand com = new SqlCommand(sql, SqlDrConn);
            temp = Convert.ToInt32(com.ExecuteScalar());
            SqlDrConn.Close();
            return temp;
        }

        //创建表
        public void createNewTab(string tabName) {
            try {
                Open();
                string sql = "create table " + tabName + "_设备表(shebeiName char(12) not null);";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                //cmd.Connection;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            //  return list;
        }

        ////是否允许导出消息记录
        //public List<string> select_export_news(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select export_news from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        ////是否允许使用客户分析
        //public List<string> select_customer_analysis(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select customer_analysis from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        ////是否允许使用群发助手
        //public List<string> select_use_assistant(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select use_assistant from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        ////是否允许查看全部群发消息
        //public List<string> select_check_all_message(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select check_all_message from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        ////是否允许删除客户记录
        //public List<string> select_delete_customer_record(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select delete_customer_record from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        ////查询用户姓名
        //public List<string> select_user_name(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select user_name from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        ////查询联系方式
        //public List<string> select_contact(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select contact from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        ////查询账户开通状态
        //public List<string> select_manager_type(string acc) {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select manager_type from [bds249611391_db].[dbo].[hquser] where account='" + acc + "'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}

        ////查询账户名
        //public List<string> selectAccount() {
        //    List<string> str = new List<string>();
        //    try {
        //        string sql = "select account from [bds249611391_db].[dbo].[hquser]";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        while (reader.Read()) {
        //            //将结果集信息添加到返回向量中
        //            str.Add(reader[0].ToString().Trim());
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception) {
        //    }
        //    return str;
        //}
        #endregion

        #region 查询子账户是否存在以及权限设置

        //判断是否存在账户名
        public bool Select_child_acct(string nike) {
            bool asd = false;
            try {
                Open();
                string sql = "select UseAcct from Child_HuanQiuYun where UseAcct = '" + nike + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    asd = true;
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return asd;
        }
        //查询该账户的密码
        public List<string> Select_child_Pass(string nike) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select UsePas from Child_HuanQiuYun where UseAcct = '" + nike + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }

        //查询该账户所属管理员
        public List<string> Select_child_Admin(string nike) {
            List<string> list = new List<string>();
            try {
                Open();
                string sql = "select Admin from Child_HuanQiuYun where UseAcct = '" + nike + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    //将结果集信息添加到返回向量中
                    list.Add(reader[0].ToString());
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }

        #endregion

        public List<WXMsg> GetChatLogs(string tabName, string to_user ) {
            List<WXMsg> list = new List<WXMsg>();
            string tbna = "ChatLog"+ tabName;
            try {
                Open();
                string sql = "select * from " + tbna + " where ToUser ='" + to_user + "'  order by Time DESC";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();

                //将结果集信息添加到返回向量中
                while (reader.Read()) {
                    WXMsg msg = new WXMsg();  //就是这个啊，气死我了，因为放到外面，每次添加的数据都一样，

                    msg.Time = Convert.ToDateTime(reader[0]);
                    msg.Fr_NickName = reader[1].ToString().Trim();
                    msg.To_NickName = reader[2].ToString().Trim();
                    msg.Msg = reader[3].ToString().Trim();
                    msg.IsReceive = int.Parse(reader[4].ToString().Trim());
                    msg.Readed = reader[5].ToString().Trim() == "1" ? true : false;
                    msg.Type = reader[6].ToString().Trim();
                    msg.FileName = reader[7].ToString().Trim();
                    msg.FileSize = reader[8].ToString().Trim();
                    msg.V_Content = reader[9].ToString().Trim();
                    msg.V_NickName = reader[10].ToString().Trim();
                    msg.V_Value = reader[11].ToString().Trim();
                    msg.V_UserTicket = reader[12].ToString().Trim();
                    msg.MsgId = reader[13].ToString().Trim();
                    msg.MediaId = reader[14].ToString().Trim();
                    msg.TxtUrl = reader[15].ToString().Trim();
                    list.Add(msg);
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
            return list;
        }

        public void insertChatLogs( string AccName, string tabName, WXMsg msg ) {
            string tbna = "ChatLog" + tabName;
            int read;
            if (msg.Readed) {
                read = 1;
            }
            else {
                read = 0;
            }
            Open();
            try {
                string sql = "insert into " + tbna + "(Time,FromUser ,ToUser,CONT ,Received,Readed,Type,FileName,FileSize,V_Content ,V_NickName,V_Value,V_UserTicket,_msgid ,MediaId,TxtUrl)VALUES ('" + msg.Time + "','" + msg.Fr_NickName + "','" + msg.To_NickName + "','" + msg.Msg + "','" + msg.IsReceive + "','" + read + "','" + msg.Type + "','" + msg.FileName + "','" + msg.FileSize + "','" + msg.V_Content + "','" + msg.V_NickName + "','" + msg.V_Value + "','" + msg.V_UserTicket + "','" + msg.MsgId + "','" + msg.MediaId + "','" + msg.TxtUrl + "')";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();  //连接关闭必须在这个的后面，负责会出现sql语句无法执行的问题
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        public void CreateNewChatTabs( string AccName, string tabName ) {
            Open();
            try {
                string tbna = "ChatLog" + tabName;
                string sql = "create table " + tbna + "(Time datetime not null ,FromUser char(100) ,ToUser char(100) ,CONT char(600) ,Received int,Readed int,Type int,FileName char(400),FileSize char(20),V_Content char(200),V_NickName char(60) ,V_Value char(600),V_UserTicket char(200),_msgid char(1000),MediaId char(1000),TxtUrl char(400));";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception) {
                throw;
            }
        }

        #region 敏感词操作
        /// <summary>
        ///创建与子账号对应的敏感词表
        /// </summary>
        /// <param name="tabName"></param>
        public void CreateNewSensitiveTab( string tabName ) {
            try {
                Open();
                string tbna = "Sensitive_" + tabName;
                string sql = "CREATE TABLE " + tbna + "(Iden int identity(1,1),AddPerson char(50), Time datetime not null ,SensitiveWords char(300) "
                    + ")";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("CreateNewSensitiveTab", e.ToString());
            }
        }

        /// <summary>
        /// 存储用户铭感词
        /// </summary>
        /// <param name="msg"></param>
        public void insertSensitive( string tabName,string Addperson, DateTime time, string sensitive ) {
            string tbna = "Sensitive_" + tabName;

            Open();
            try {
                string sql = "insert into " + tbna + "( AddPerson,Time,SensitiveWords) VALUES ('" + Addperson + "','" + time.ToString("s") + "','" + sensitive + "'" + ")";

                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();  //连接关闭必须在这个的后面，负责会出现sql语句无法执行的问题
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("insertSensitive", e.ToString());
            }
        }

        /// <summary>
        /// 从特定表获取用户铭感词
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="to_user"></param>
        /// <returns></returns>
        public List<string> GetSensitive( string tabName ) {
            List<string> Senlist = new List<string>();
            string tbna = "Sensitive_" + tabName;
            string sensitive;
            try {
                Open();
                string sql = "select SensitiveWords from " + tbna;
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                   // MessageBox.Show(reader["SensitiveWords"].ToString());
                    sensitive = reader["SensitiveWords"].ToString();
                    Senlist.Add(sensitive);
                }
                reader.Close();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("GetSensitive:获取记录", e.ToString());
            }
            return Senlist;
        }

        /// <summary>
        /// 删除用户敏感词
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="sensitive"></param>
        public void DeleteSensitive( string tabName, string sensitive ) {
            Open();
            try {
                string tbna = "Sensitive_" + tabName;
                string sql = "delete  from " + tbna + " where SensitiveWords='" + sensitive + "'";
                SqlCommand cmd = new SqlCommand(sql, SqlDrConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SqlDrConn.Close();
            }
            catch (Exception e) {
                WriteLog.Writelog("DeleteChatLog:删除聊天记录", e.ToString());
            }
        }
        #endregion
    }
}